<?php
/*
 * @Author: your name
 * @Date: 2021-11-03 15:16:15
 * @LastEditTime: 2021-12-27 10:04:12
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: \property\app\Models\admin_model\Column.php
 */

namespace App\Models\admin_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class FindShopSubscribe extends Model
{
    use HasFactory;
    static string $tableName = 'find_shop_subscribe';

    public static function subscribeAdd($formData){
           return DB::table(self::$tableName)->insert($formData);
    }

    public static function subscribeList($whereStr = '',$limitStr = ''){
       //     echo 
           return DB::select(' SELECT `all`.* ,`subscribe`.*,`find_shop_user`.`nickName`,`find_shop_user`.`avatarUrl`,`find_shop_user`.`source` FROM ( SELECT "housing" AS `type`,`building`.`address` ,`housing`.`id` AS `target_id`, CONCAT( `housing`.`spaceNum`, "㎡ · ", `housing`.`level_des`, " · ", `building`.`title` ) AS `title` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` UNION ALL SELECT "store" AS `type`,`store`.`address` ,`store`.`id` AS `target_id`, CONCAT( `store`.`spaceNum`, "㎡ · ", `store`.`title` ) AS `title` FROM `store` UNION ALL SELECT "building" AS `type`, `building`.`address`, `building`.`id` AS `target_id`, CONCAT( IFNULL(MIN(`housing`.`spaceNum`),0),"~", IFNULL(MAX(`housing`.`spaceNum`),0), "㎡ · ", `building`.`title` ) AS `title` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` AND `housing`.`shelves` = 1 AND `housing`.`delete_time` is null GROUP BY `building`.`id` ) AS `all` INNER JOIN `find_shop_subscribe` AS `subscribe` ON `subscribe`.`target_id` = `all`.`target_id` AND `subscribe`.`table` = `all`.`type` LEFT JOIN `find_shop_user` ON `subscribe`.`user_openid` = `find_shop_user`.`openid` WHERE `subscribe`.`delete_time` is null '.$whereStr.'  ORDER BY `subscribe`.`top_time` DESC,  `subscribe`.`id` DESC  '.$limitStr);
    }

    public static function subscribeCount($whereStr = ''){
           return DB::select('SELECT COUNT(*) as `count` FROM ( SELECT "housing" AS `type`, `housing`.`id` AS `target_id`, CONCAT( `housing`.`spaceNum`, "㎡ · ", `housing`.`level_des`, " · ", `building`.`title` ) AS `title` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` UNION ALL SELECT "store" AS `type`, `store`.`id` AS `target_id`, CONCAT( `store`.`spaceNum`, "㎡ · ", `store`.`title` ) AS `title` FROM `store` ) AS `all` INNER JOIN `find_shop_subscribe` AS `subscribe` ON `subscribe`.`target_id` = `all`.`target_id` AND `subscribe`.`table` = `all`.`type` LEFT JOIN `find_shop_user` ON `subscribe`.`user_openid` = `find_shop_user`.`openid` WHERE `subscribe`.`delete_time` is null  '.$whereStr);
    }

    public static function subscribePlan($openid,$whereStr = '',$limitStr = ''){
       return DB::select(' SELECT `all`.*, `subscribe`.* FROM ( SELECT "housing" AS `type`, `building`.`address`, `housing`.`id` AS `target_id`, CONCAT( `housing`.`spaceNum`, "㎡ · ", `housing`.`level_des`, " · ", `building`.`title` ) AS `title` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` UNION ALL SELECT "store" AS `type`, `store`.`address`, `store`.`id` AS `target_id`, CONCAT( `store`.`spaceNum`, "㎡ · ", `store`.`title` ) AS `title` FROM `store` UNION ALL SELECT "building" AS `type`, `building`.`address`, `building`.`id` AS `target_id`, CONCAT( IFNULL(MIN(`housing`.`spaceNum`),0),"~", IFNULL(MAX(`housing`.`spaceNum`),0), "㎡ · ", `building`.`title` ) AS `title` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` AND `housing`.`shelves` = 1 AND `housing`.`delete_time` is null GROUP BY `building`.`id` ) AS `all` INNER JOIN `find_shop_subscribe` AS `subscribe` ON `subscribe`.`target_id` = `all`.`target_id` AND `subscribe`.`table` = `all`.`type` WHERE `subscribe`.`delete_time` IS NULL AND `subscribe`.`user_openid` = "'.$openid.'" '.$whereStr.' ORDER BY  `subscribe`.`id` DESC '.$limitStr);

    }

  
}
